package com.andersen.database.dao;

import com.andersen.database.entity.EmployeeEntity;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.andersen.database.entity.OfficeEntity;
import org.springframework.stereotype.Component;

/**
 * DAO Сотрудника
 *
 * @author Corvus 11,Georg
 */
@Component
public class EmployeeDao extends AbstractDao{


    /**
     * Вставка объекта в БД
     * @param employee
     * @return
     */
    public long insertEmployee(EmployeeEntity employee) {

        LOG.info("EmployeeDao.insertEmployee()");
        LOG.info(employee);

        long newKey = ID_INVALID;

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        StringBuilder sql = new StringBuilder();

        sql.append("INSERT INTO employee(");
            sql.append("name, ");
            sql.append("surname, ");
            sql.append("patronymic, ");
            sql.append("position, ");
            sql.append("skype, ");
            sql.append("phone, ");
            sql.append("office_id) ");
        sql.append("VALUE(?,?,?,?,?,?,?);");

        try {
            conn = dataSource.getConnection();
            st = conn.prepareStatement(sql.toString(),PreparedStatement.RETURN_GENERATED_KEYS);
            st.setString(1, employee.getName());
            st.setString(2, employee.getSurname());
            st.setString(3, employee.getPatronymic());
            st.setString(4, employee.getPosition());
            st.setString(5, employee.getSkype());
            st.setString(6, employee.getPhone());
            st.setLong(7, employee.getOfficeId());
            if (st.executeUpdate() > 0) {
                rs = st.getGeneratedKeys();
                rs.first();
                newKey = rs.getLong(1);
            }
        } catch(SQLException exc) {
              LOG.error("Error EmployeeDao.insertEmployee()", exc);
        } finally {
            safeClose(rs);
            safeClose(st);
            safeClose(conn);
        }
        return newKey;
    }

    /**
     * Изменить уже существующую строку в БД
     * @param employee объект с заполненным id и новыми свойствами
     * @return
     */
    public long updateEmloyee(EmployeeEntity employee){

        LOG.info("EmployeeDao.updateEmloyee()");
        LOG.info(employee);

        long id = ID_INVALID;

        StringBuilder sql = new StringBuilder();

        sql.append("UPDATE employee ");
            sql.append("SET name = ?, ");
            sql.append("surname = ?, ");
            sql.append("patronymic = ?, ");
            sql.append("position = ?, ");
            sql.append("skype = ?, ");
            sql.append("phone = ?, ");
            sql.append("office_id = ?, ");
            sql.append("discharged = ? ");
        sql.append("WHERE id = ?;");

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = dataSource.getConnection();
            st = conn.prepareStatement(sql.toString(),PreparedStatement.RETURN_GENERATED_KEYS);
            st.setString(1, employee.getName());
            st.setString(2, employee.getSurname());
            st.setString(3, employee.getPatronymic());
            st.setString(4, employee.getPosition());
            st.setString(5, employee.getSkype());
            st.setString(6, employee.getPhone());
            st.setLong(7, employee.getOfficeId());
            st.setBoolean(8, employee.isDischarged());
            st.setLong(9, employee.getId());
            if (st.executeUpdate() == 0) {
                LOG.error("Error with EmployeeDao.updateEmloyee() - no rows updated.");
            }
        } catch(SQLException exc) {
            LOG.error("Error with EmployeeDao.updateEmloyee()", exc);
        } finally {
            safeClose(rs);
            safeClose(st);
            safeClose(conn);
        }
        return id;
    }

    /**
     * Создать объект по ResultSet с результатами запроса
     * @param rset
     * @return сущность с заполненными полями свойств
     * @throws SQLException
     */
    private EmployeeEntity mapResultSetRow(ResultSet rset) throws SQLException {
        EmployeeEntity e = new EmployeeEntity();
        e.setId(rset.getLong("id"));
        e.setName(rset.getString("name"));
        e.setSurname(rset.getString("surname"));
        e.setPatronymic(rset.getString("patronymic"));
        e.setPosition(rset.getString("position"));
        e.setSkype(rset.getString("skype"));
        e.setPhone(rset.getString("phone"));
        e.setOfficeId(rset.getLong("office_id"));
        e.setDischarged(rset.getBoolean("discharged"));
        return e;
    }

    /**
     * Получить Employee по Id
     * @param employeeId
     */
    public EmployeeEntity getEmployee(long employeeId){
        LOG.info("getting Employee from db with id " + employeeId );

        String sql = "SELECT * FROM employee e WHERE e.id = ?";

        EmployeeEntity employee = null;

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setLong(1, employeeId);
            rs = ps.executeQuery();
            if (rs.next()) {
                employee = mapResultSetRow(rs);
            }
        } catch (SQLException e) {
            LOG.error("Error with EmployeeDao.getEmployee()", e);
        } finally {
            safeClose(rs);
            safeClose(ps);
            safeClose(conn);
        }
        return employee;
    }

    /**
     * Получить Список сотрудников
     * @param from_item с позиции
     * @param //to_item по позицию
     * @param sort_by отсортированых по полю
     * @param sort_order в порядке возр\убыв
     */
    public ArrayList<EmployeeEntity> getQueryEmployees(int from_item, int count, int sort_by, boolean sort_order,boolean discharge) {


        LOG.info("getQueryEmployees from db from_item "+ from_item +" count "+ count +" sort_by "+ sort_by +" sort_order "+ sort_order);

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT e.id, ");
            sql.append("e.name, ");
            sql.append("e.surname, ");
            sql.append("e.patronymic, ");
            sql.append("e.position, ");
            sql.append("e.skype, ");
            sql.append("e.phone, ");
            sql.append("e.office_id, ");
            sql.append("e.discharged, ");
            sql.append("o.name ");
        sql.append("FROM office o, employee e ");
        sql.append("WHERE e.office_id = o.id");
        if (discharge == false)
            sql.append(" and e.discharged = 0");


        String[] columns = {"e.id",
                            "e.name",
                            "e.surname",
                            "e.patronymic",
                            "e.position",
                            "e.skype",
                            "e.phone",
                            "o.discharge",
                            "o.name"};

        int order = (sort_order)? SORT_ORDER_ASC : SORT_ORDER_DESC;

        appendSortOrder(sql, columns[sort_by], order);
        appendPaginationParams(sql, from_item, count);

        sql.append(";");

        LOG.info(sql.toString());

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        ArrayList<EmployeeEntity> employeeList = new ArrayList<EmployeeEntity>();

        try {
            conn = dataSource.getConnection();
            st = conn.prepareStatement(sql.toString());
            rs = st.executeQuery();

            while (rs.next()) {
                EmployeeEntity employee = new EmployeeEntity();

                employee.setId(rs.getLong(1));
                employee.setName(rs.getString(2));
                employee.setSurname(rs.getString(3));
                employee.setPatronymic(rs.getString(4));
                employee.setPosition(rs.getString(5));
                employee.setSkype(rs.getString(6));
                employee.setPhone(rs.getString(7));
                employee.setOfficeId(rs.getLong(8));
                employee.setDischarged(rs.getBoolean(9));
                OfficeEntity office = new OfficeEntity();
                office.setName(rs.getString(10));
                employee.setOffice(office);

                employeeList.add(employee);
            }
        } catch (SQLException exc) {
            LOG.error("Ошибка при выполнении EmployeeDao.getQueryEmployees()",exc);
            return null;
        } finally {
            safeClose(rs);
            safeClose(st);
            safeClose(conn);
        }

        return employeeList;
    }

    public long getEmployeeCount() {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        long cnt = -1;

        try {
            conn = dataSource.getConnection();
            st = conn.prepareStatement("SELECT COUNT(id) FROM equipment.employee;");
            rs = st.executeQuery();
            if (rs.first())
                cnt = rs.getLong(1);
        }catch(SQLException exc) {
            LOG.error("Ошибка подсчета размера таблицы",exc);
            cnt = -1;
        } finally {
            safeClose(rs);
            safeClose(st);
            safeClose(conn);
        }
        return cnt;
    }

    public ArrayList<EmployeeEntity> getEmployeesForAccounts(long officeId) {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        ArrayList<EmployeeEntity> list = new ArrayList<EmployeeEntity>();

        StringBuilder sql = new StringBuilder();

        sql.append("SELECT * FROM employee e ");
        sql.append("WHERE (e.office_id = (?)) ");
            sql.append("AND (e.discharged = 0) ");
            sql.append("AND e.id NOT IN ");
                sql.append("(SELECT u.employee_id FROM user u) ");
        sql.append("ORDER BY e.surname, e.name, e.patronymic;");

        try {

            conn = dataSource.getConnection();
            st = conn.prepareStatement(sql.toString());
            st.setLong(1, officeId);
            rs = st.executeQuery();

            while (rs.next()) {
                EmployeeEntity employee = mapResultSetRow(rs);
                list.add(employee);
            }
        } catch (SQLException exc) {
            LOG.error("Ошибка при выполнении EmployeeDao.getEmployeesFrom()",exc);
            return null;
        } finally {
            safeClose(rs);
            safeClose(st);
            safeClose(conn);
        }
        return list;
    }

    public ArrayList<EmployeeEntity> getWorkingEmployees(long officeId) {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        ArrayList<EmployeeEntity> list = new ArrayList<EmployeeEntity>();

        StringBuilder sql = new StringBuilder();

        sql.append("SELECT * FROM employee e ");
        sql.append("WHERE (e.office_id = ?) AND (e.discharged = 0) ");
        sql.append("ORDER BY e.surname, e.name, e.patronymic;");

        try {

            conn = dataSource.getConnection();
            st = conn.prepareStatement(sql.toString());
            st.setLong(1, officeId);
            rs = st.executeQuery();

            while (rs.next()) {
                EmployeeEntity employee = mapResultSetRow(rs);
                list.add(employee);
            }
        } catch (SQLException exc) {
            LOG.error("Ошибка при выполнении getWorkingEmployees",exc);
            return null;
        } finally {
            safeClose(rs);
            safeClose(st);
            safeClose(conn);
        }
        return list;
    }

    public boolean dischargeEmployee(long id) {

        Connection conn = null;
        PreparedStatement st = null;
        ArrayList<EmployeeEntity> list = new ArrayList<EmployeeEntity>();

        String sql = "UPDATE equipment.employee SET discharged = NOT discharged WHERE id= ?;";
        try {

            conn = dataSource.getConnection();
            st = conn.prepareStatement(sql);
            st.setLong(1, id);
            st.executeUpdate();

        } catch (SQLException exc) {
            LOG.error("Ошибка при выполнении dischargeEmployee",exc);
            return false;
        } finally {
            safeClose(st);
            safeClose(conn);
        }

        return true;

    }


    // Получение ФИО менеджера по id
    public String getFIOManager(long id){

        String fio ="";
        LOG.info("EmployeeDao.getFIOManager()");

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        StringBuilder sql = new StringBuilder();

        sql.append("SELECT concat(employee.surname, ' ', employee.name, ' ', employee.patronymic) AS FIO ");
        sql.append("FROM equipment.employee ");
        sql.append("WHERE employee.position = 'Менеджер' AND employee.id = ?;");

        try{

            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setLong(1, id);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                //ComponentTypeEntity componentTypeEntity = mapResultSetRow(resultSet);
                //componentSubTypeArrayList.add(componentTypeEntity);
                fio = resultSet.getString("FIO");
            }

        }catch(SQLException exc) {
            LOG.error("Error ComponentTypeDao.getNumberOfHeirsSubType()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }
        return fio;
    }

    // Получение списка менеджеров (id ФИО)
    public List<EmployeeEntity> getManagerList(){

        LOG.info("EmployeeDao.getManagerList()");

        List<EmployeeEntity> listEmployee = new ArrayList<EmployeeEntity>();

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        StringBuilder sql = new StringBuilder();

        sql.append("SELECT employee.id, concat(employee.surname, ' ', employee.name, ' ', employee.patronymic) AS FIO ");
        sql.append("FROM equipment.employee ");
        sql.append("WHERE employee.position = 'Менеджер';");

        try{

            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                EmployeeEntity employeeEntity = new EmployeeEntity();

                employeeEntity.setId(resultSet.getLong("id"));
                employeeEntity.setName(resultSet.getString("FIO"));

                listEmployee.add(employeeEntity);
            }

        }catch(SQLException exc) {
            LOG.error("Error ComponentTypeDao.getNumberOfHeirsSubType()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }
        return listEmployee;
    }

}
